﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;

namespace Exe1
{
    public class MyDBContainer:MyContainer
    {
        private OleDbConnection _cnn;
        public MyDBContainer(string dbFileName)
        {
            // connect the database
            string cnnstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +dbFileName+ ";User Id=admin;Password=;";
            _cnn = new OleDbConnection(cnnstr);
            _cnn.Open();

        }        
        ~MyDBContainer()
        {
            // disconnect the database
            //_cnn.Close();
        }

        public override int getCount()
        {
            int cnt=0;

            OleDbCommand cmd = _cnn.CreateCommand();
            cmd.CommandText = "select * from papers";

            OleDbDataReader reader = cmd.ExecuteReader();
            
            while (reader.Read())
            {
                cnt++;
            }

            return cnt;
        }

        public override itm get(int idx)
        {
            int cnt = 0;
            itm rt;
            rt.title = "";
            rt.authors = "";
            rt.zhaiyao = "";

            OleDbCommand cmd = _cnn.CreateCommand();
            cmd.CommandText = "select * from papers";

            OleDbDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                if (cnt == idx)
                {
                    rt.title = reader.GetString(1);
                    rt.authors = reader.GetString(2);
                    rt.zhaiyao = reader.GetString(3);

                    break;
                }
                cnt++;
            }

            return rt;
        }

        public override bool add(itm v)
        {
            bool rt = true;

            OleDbCommand cmd = _cnn.CreateCommand();
            cmd.CommandText = "insert into papers (title, authors, abstract) values('" + v.title +"', '" + v.authors+"', '"+v.zhaiyao+"') ";

            cmd.ExecuteNonQuery();

            return rt;
        }
        public override bool set(int idx, itm v)
        {
            bool rt = true;

            // 获取第idx个记录的具体内容
            itm content = get(idx);
            OleDbCommand cmd = _cnn.CreateCommand();
            cmd.CommandText = "update papers set [title] = '" + v.title +"', [authors] = '"+v.authors+"', [abstract] = '"+ v.zhaiyao+"' where [title] = '" + content.title+"' and [abstract] ='" +content.zhaiyao +"'";

            cmd.ExecuteNonQuery();
            
            return rt;
        }
        public override ArrayList query(string queryS)
        {
            ArrayList rt = new ArrayList();
            itm v;
            v.title = "";
            v.authors = "";
            v.zhaiyao = "";

            OleDbCommand cmd = _cnn.CreateCommand();
            cmd.CommandText = "select * from papers";

            OleDbDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                v.title = reader.GetString(1);
                v.authors = reader.GetString(2);
                v.zhaiyao = reader.GetString(3);

                if (CompString(v.title, queryS) || CompString(v.authors, queryS) || CompString(v.zhaiyao, queryS))
                {
                    rt.Add(v);
                }
                
            }            

            return rt;
        }

        public override bool remove(int idx)
        {
            bool rt=true;
            itm content = get(idx);
            OleDbCommand cmd = _cnn.CreateCommand();
            cmd.CommandText = "delete from papers where([title]='" + content.title + "' and [abstract] = '"+content.zhaiyao+"')";
            cmd.ExecuteNonQuery();

            return rt;
        }
    }
}
